import * as XLSX from 'xlsx'
// var XLSX = require("xlsx");

/**
 * 将特定格式的json数据转存excel文件: 
 * @param {JSON} json
 */
function exportExcel(json) {
	const workbook = XLSX.utils.book_new();
	const num = json['num'];
	for (let i = 0; i < num; i++) {
		//解析sheet
		const sheet = json['sheets'][i];
		let type = sheet['type'];
		let worksheet;
		if (type === 'json') {
			worksheet = XLSX.utils.json_to_sheet(sheet['data']);
			//替换表头
			XLSX.utils.sheet_add_aoa(worksheet, [sheet['header']], {
				origin: "A1"
			});
		} else { //array
			worksheet = XLSX.utils.aoa_to_sheet(sheet['data']);
		}

		//设置表头宽度
		worksheet["!cols"] = sheet['width'].map(w => ({
			wch: w
		})); //[ { wch: 10 }, {wch: 20} ]

		//设置表头高度
		worksheet["!rows"] = [{
			hpt: 25
		}];

		// //冻结表头
		// worksheet["!freeze"] = {
		// 	xSplit: "1", // 代表固定哪一列，设置为0就不生效了，不知道为什么，必须要固定一列
		// 	ySplit: "1", // 代表固定前几行
		// 	topLeftCell: "B2", //在未冻结区域的左上角显示的单元格，默认为第一个未冻结的单元格
		// 	state: "frozen"
		// };

		// //style
		// worksheet["A1"]["s"] = {
		// 	alignment: {
		// 		horizontal: 'center',
		// 		vertical: 'center',
		// 		wrapText: true
		// 	}//设置标题水平竖直方向居中，并自动换行展示
		// };

		XLSX.utils.book_append_sheet(workbook, worksheet, sheet['name']);
	}
	// console.log(workbook);

	XLSX.writeFile(workbook, json['name'], {
		bookType: 'xlsx'
	});
}

/**
 * 读取本地excel文件
 * @param {*} file 
 * @param {*} callback 
 */
function importEcxel(file, callback) {
	var reader = new FileReader();
	reader.onload = function(e) {
		var data = e.target.result;
		var workbook = XLSX.read(data, {
			type: 'binary',
			cellText: false, //关闭可将时间格式变为指定格式
			cellDates: true //data -> 中国标准时间
			// raw: true//date -> 时间戳格式
		});
		//返回map
		if (callback) {
			callback(feedBack(workbook));
		}
	}
	reader.readAsBinaryString(file);
}

/**
 * @param workbook object
 */
function feedBack(workbook) {
	let array = [];
	workbook.SheetNames.forEach(function(value, index) {
		let item = new Object();
		item.index = index;
		item.name = value;
		item.data = XLSX.utils.sheet_to_json(workbook.Sheets[value], {
			raw: false,
			dateNF: 'yyyy-MM-dd HH:mm:ss'
		});
		//生成json表格内容
		// console.log('表：' + index + ", " + value);
		array.push(item);
	});

	return array;
}

/**
 * 直接将表格内数据导出
 * @param {*} table
 * @param {*} widths 列宽
 * @param {*} name 
 */
function tableToExcel(table, widths, name) {
	// let workbook = XLSX.utils.table_to_book(table, {
	// 	dateNF: 'yyyy-MM-dd HH:mm:ss'
	// });
	const workbook = XLSX.utils.book_new();
	let worksheet = XLSX.utils.table_to_sheet(table, {
		dateNF: 'yyyy-MM-dd HH:mm:ss'
	})
	
	//设置表头宽度
	worksheet["!cols"] = widths;
	
	//设置表头高度
	worksheet["!rows"] = [{ hpt: 20 }];
	
	XLSX.utils.book_append_sheet(workbook, worksheet, name);

	XLSX.writeFile(workbook, name + ".xlsx", {
		bookType: 'xlsx'
	});
}

function tableToExcel2(table, name) {
	let uri = 'data:application/vnd.ms-excel;base64,';
	let template =
		'<html xmlns:o="urn:schemas-microsoft-com:office:office" ' +
		'xmlns:x="urn:schemas-microsoft-com:office:excel" ' +
		'xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]>' +
		'<xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>' +
		'<x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions>' +
		'</x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body>' +
		'<table>{table}</table></body></html>';

	let base64 = function(s) {
		return window.btoa(unescape(encodeURIComponent(s)))
	};
	let format = function(s, c) {
		return s.replace(/{(\w+)}/g,
			function(m, p) {
				return c[p];
			})
	};

	//根据正则表达式，把style='mso-number-format:"\@"' 塞入td中,将数据转成String
	table.innerHTML = table.innerHTML.replace(
		/<td/g,
		"<td STYLE='MSO-NUMBER-FORMAT:\\@'"
	);

	let ctx = {
		worksheet: name,
		table: table.innerHTML,
	}

	// window.location.href = uri + base64(format(template, ctx));
	let link = document.createElement('a');
	link.href = uri + base64(format(template, ctx));
	link.download = name + '.xls'; //重命名
	document.body.appendChild(link);
	link.click();
	document.body.removeChild(link);
}

export {
	importEcxel,
	exportExcel,
	tableToExcel
}
